SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


/** Create Stored Procedures **/
CREATE PROCEDURE dbo.[DnnProduct_CDS_AssetsListAll]
	@DistributorID varchar(50),
	@CategoryID int,
	@WhereClause nvarchar(1000),
	@SortExpression varchar(50),
	@MaximumRows int = null, 
	@StartRowIndex int = null
AS
BEGIN
	SET NOCOUNT ON;

	IF (@MaximumRows IS NULL) BEGIN SET @MaximumRows = 1000 END
	IF (@StartRowIndex IS NULL) BEGIN SET @StartRowIndex = 0 END

	IF (@CategoryID > 0)
	BEGIN
		CREATE TABLE #temp (ID int);
	
		WITH CTE (ID, ParentID)
		AS
		(
			SELECT ID, ParentID FROM [DnnProduct_CDS_Categories]
			WHERE ID = @CategoryID
			UNION ALL
			SELECT c.ID, c.ParentID FROM [DnnProduct_CDS_Categories] c
			JOIN CTE ON CTE.ID = c.ParentID
		)
		INSERT INTO #temp
		SELECT ID FROM CTE
	END

	DECLARE @SQLString NVARCHAR(1000)
	DECLARE @ParmDefinition NVARCHAR(100)

	/* Build the SQL string once.*/
	SET @SQLString = N'WITH ItemRN AS (SELECT ROW_NUMBER() OVER(ORDER BY '+ @SortExpression + N') AS RowNum, A.CategoryID, A.ResourceID, Title, Format, Creator, Publisher, FileSize, SKU, DRMType, Link, A.[ID], PackageStatus, T.[Name] AS TemplateName, Identifier, Archived, C.[Name] as Category FROM [DnnProduct_CDS_Assets] A LEFT OUTER JOIN [DnnProduct_CDS_Templates] T on A.TemplateID = T.[ID] LEFT OUTER JOIN [DnnProduct_CDS_Categories] C on A.CategoryID = C.[ID] WHERE ((DRMType = 1 AND ResourceID IS NOT NULL) OR DRMType <> 1)'
	
	IF (LEN(@DistributorID) > 0) BEGIN SET @SQLString = @SQLString + N' and A.DistributorID = @DistributorID' END

	IF (@CategoryID > 0)
	BEGIN
		SET @SQLString = @SQLString + N' and A.CategoryID IN (SELECT ID FROM #temp)'
	END

	IF (LEN(@WhereClause) > 0)
	BEGIN
		SET @SQLString = @SQLString + N' AND ' + @WhereClause
	END

	SET @SQLString = @SQLString + N' ) SELECT CategoryID, ResourceID, Title, Format, Creator, Publisher, FileSize, SKU, DRMType, Link, [ID], PackageStatus, TemplateName, Identifier, Archived, Category FROM ItemRN WHERE RowNum BETWEEN @startRowIndex+1 AND (@startRowIndex+@maximumRows);'
	SET @ParmDefinition = N'@MaximumRows int, @StartRowIndex int'
	
	IF (LEN(@DistributorID) > 0) BEGIN SET @ParmDefinition = @ParmDefinition + N', @DistributorID varchar(50)' END
	
	IF (LEN(@DistributorID) > 0) 
		BEGIN
			EXECUTE sp_executesql @SQLString, @ParmDefinition, @MaximumRows, @StartRowIndex, @DistributorID
		END
	ELSE
		BEGIN
			EXECUTE sp_executesql @SQLString, @ParmDefinition, @MaximumRows, @StartRowIndex
		END
	
	
	IF (@CategoryID > 0)
	BEGIN
		DROP TABLE #temp
	END
END
GO
